How can I create backup script for selected tables in Oracle?

Question

Aryan Kumar · Answer

Sure, here is how you can create a backup script for selected tables in Oracle:
SQL
-- Create a backup script for selected tables.
SET SERVEROUTPUT ON;
DECLARE
  v_table_name VARCHAR2(30);
BEGIN
  -- Loop through the list of tables and create a backup for each one.
  FOR v_table_name IN ('table1', 'table2', 'table3') LOOP
    dbms_output.put_line('Backing up table ' || v_table_name);
    EXECUTE IMMEDIATE 'CREATE TABLE BAK_' || v_table_name || ' AS SELECT * FROM ' || v_table_name;
  END LOOP;
END;
/
This script will create a backup for the tables table1, table2, and
table3. The backup tables will be named BAK_table1, 
BAK_table2, and BAK_table3.
To run the script, you would save it as a text file and then execute it from SQLPlus. For example, if you saved the script as
backup_script.sql, you would run it by typing the following command into SQLPlus:
SQL
@backup_script.sql
This would create the backup tables for the specified tables.
Here is an explanation of the code in the script:
The SET SERVEROUTPUT ON statement tells Oracle to display output from the script.The DECLARE statement declares a variable named v_table_name to hold the name of the current table.The FOR loop iterates through the list of tables and creates a backup for each one.The dbms_output.put_line() procedure displays a message to the console.The EXECUTE IMMEDIATE statement executes the specified SQL statement.The END statement marks the end of the FOR loop.

forum

How can I create backup script for selected tables in Oracle?

Utpal Vishwas

Can you answer this question?

1 Answers

Liked By